# -*-coding:utf-8-*-
"""
处理将上证和深证A股分别的code代码分别在前面+sh,sz
追加输入至数据库
"""


import pandas as pd
from sqlalchemy import create_engine

import traceback
import numpy as np
import os
# 将数据写入数据库


class HanderCodeName:
    # 添加记录数据表
    tab_record = 'code_name_record'
    tab_main='code_name'

    def __init__(self, cfg=None,):
        self.con = create_engine(
            'mysql+pymysql://'+cfg.CONFIG['db']['default']['username']+':'+cfg.CONFIG['db']['default']['pwd']+'@'+cfg.CONFIG['db']['default']['host']+':'+cfg.CONFIG['db']['default']['port']+'/'+cfg.CONFIG['db']['default']['dbname']+'?charset=utf8')
       
        self.cfg = cfg
    

    def setCodeFile(self, codefile):
        """
        设置要导入的codefile
        """
        self.codefile = codefile

    def toDb(self):
        # 处理数据
        # 判断是上证还是深证
        # 循环进行拼接标识符
        # 进行入库操作

        if(self._isAdd()):  # 如果已经添加了就不重新添加
            return False

        pd_data2 = self._joinCode()
        # 添加至主数据库
        self._dataframe_tosql(pd_data2, self.tab_main)
        # 添加记录至数据库
        self._record_to_sql()

    def _record_to_sql(self):
        """
        将记录添加至数据库
        """
        file_name = self.codefile.split('/')[-1]
        data = {'record': file_name}
        pd_record = pd.DataFrame(data, columns=['record'], index=[0])
        self._dataframe_tosql(pd_record, self.tab_record)

    def _dataframe_tosql(self, pd_data2, table_name):
        """
        将pandas  dataFrame 数据直接写入数据库
        """
        try:

            pd.io.sql.to_sql(pd_data2, table_name, self.con,
                             if_exists="append", index=False)
        except Exception as ee:
            print("导入数据库失败")
            print("error >>>", ee)
            traceback.print_exc()
        else:
            print("导入数据库-end---"+table_name)
        finally:
            return 1

    def _getCode(self):
        # 读取csv股票代码
        data = pd.read_csv(self.codefile, dtype={'code': str})
        return data

    def _isShOrSZ(self):
        # 获取文件名称
        # 以sh开头为上证
        # 以sz开头为深证
        self.file_name = os.path.basename(self.codefile)  # 返回文件名
        self.file_str_list = self.file_name.split('_')
        if(self.file_str_list[0] == 'sh'):
            return self.cfg.CONFIG['mark']['sh']
        elif(self.file_str_list[0] == 'sz'):
            return self.cfg.CONFIG['mark']['sz']

    def _joinCode(self):
        # 拼接字符串
        bs = self._isShOrSZ()
        data = self._getCode()
        self._addCreateTime()

        data['code'] = [bs+'%s' % i for i in data['code']]  # 拼接字符串
        data['add_time'] = self.add_time
        return data

    def _addCreateTime(self):
        # 添加时间
        add_time = self.file_str_list[-1].split('.')[0]
        self.add_time = add_time

    def __del__(self):
        """
        关闭数据库连接
        """
        # self.con.dispose()

    def _isAdd(self):
        '''
        是否已经添加过数据库
        使用文件名称进行判断
        '''
        file_name = self.codefile.split('/')[-1]
        # print(file_name)
        data = self._select_by_filename(file_name)
        if data.empty:  # 判断DF 是否为空
            return False
        return True

    def _select_by_filename(self, filename):
        """
        查询数据库中是否有记录
        """
        sql_ = "select * from `{0}` where record='{1}'".format(
            self.tab_record, filename)

        data = pd.read_sql(sql_, self.con, columns='id')
        return data

    def get_all_codename(self):
        """
        获取所有的股票代码
        """
        sql_="select * from `{0}`".format(self.tab_main)
        data=pd.read_sql(sql_, self.con, columns=['code','name'])
        return data
        




def dataframe_tosql(pd_data2, table_name):
    """
    将pandas  dataFrame 数据直接写入数据库
    """
    try:

        pd.io.sql.to_sql(pd_data2, table_name, con,
                         if_exists="append", index=False)
    except Exception as ee:
        print("导入数据库失败")
        print("error >>>", ee)
        traceback.print_exc()
    else:
        print("导入数据库-end---"+table_name)
    finally:
        pass


def getdata_from_sql(sql_):
    """
    从数据库返回 DataFrame 数据
    """
    data = pd.read_sql(sql_, con)
    return data


def getdata_from_sql_query(sql_, columns):
    """
    从数据库返回 DataFrame 数据
    """

    data = pd.read_sql(sql_, con, columns=columns)
    return data


def getdata_from_sql_tolist(sql_):
    """
    从数据库返回 list数据
    """
    data = getdata_from_sql(sql_)
    data_ = np.array(data)
    data_list = data_.tolist()
    return data_list
